package com.example.springboot.service.impl;

import com.example.springboot.utils.ExcelUtils;
import com.example.springboot.loader.FileLoader;
import com.example.springboot.service.ExcelOperateService;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.logging.Logger;
import java.util.stream.Collectors;

/**
 * Excel表格操作
 */
@Service
public class ExcelOperateServiceImpl implements ExcelOperateService {
    @Autowired
    private FileLoader fileLoader;
    @Autowired
    private ExcelUtils excelUtils;
    private static final Logger logger = Logger.getLogger(ExcelOperateServiceImpl.class.getName());


    /**
     * 读取第一、二列数据
     *
     * @param path
     * @throws IOException
     */
    @Override
    public void readExcel(String path) throws IOException {
        Map<String, String> targetMap = new HashMap<>();
        int startRow = 2;
        logger.info("当前操作文件地址:" + path);
        File file = fileLoader.loadFile(path);
        int[] columnsToDelete = {0, 1};
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(fis);
        Sheet sheet = workbook.getSheetAt(2);
        for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell keyCell = row.getCell(columnsToDelete[0]);
                Cell valueCell = row.getCell(columnsToDelete[1]);
                if (keyCell != null && valueCell != null) {
                    String key = excelUtils.getValueAsString(keyCell);
                    String value = excelUtils.getValueAsString(valueCell);
                    targetMap.put(key, value);
                }
            }
        }
        Map<String, String> sortedMap = targetMap.entrySet()
                .stream()
                .sorted(Map.Entry.comparingByKey())
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        Map.Entry::getValue,
                        (oldValue, newValue) -> oldValue,
                        LinkedHashMap::new));
        logger.info("targetMap======>" + sortedMap);
    }

    /**
     * 写入excel文件
     *
     * @param path
     */
    @Override
    public void writeExcel(String path) throws IOException {
        /*组装需要的数据开始*/
        Map<String, String> targetMap = new HashMap<>();
        targetMap.put("A", "小王");
        targetMap.put("B", "小王");
        targetMap.put("C", "小王");
        targetMap.put("D", "小王");
        /*组装需要的数据结束*/
        int startRow = 2;
        logger.info("当前操作文件地址:" + path);
        File file = fileLoader.loadFile(path);
        int[] columnsToDelete = {13, 14};
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(fis);
        Sheet sheet = workbook.getSheetAt(2);
        for (Map.Entry<String, String> entry : targetMap.entrySet()
        ) {
            String key = entry.getKey();
            String value = entry.getValue();//
            Row row = sheet.createRow(startRow++);
            Cell cellkey = row.createCell(columnsToDelete[0]);
            Cell cellValue = row.createCell(columnsToDelete[1]);
            cellkey.setCellValue(key);
            cellValue.setCellValue(value);
        }
        fis.close();

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();

        System.out.println("数据已成功添加到 Excel 中！");
    }
}

